﻿Imports System.Data
Imports System.Data.SqlClient

Public Class frmMain
    Private Structure Table
        Dim DA As SqlDataAdapter
        Dim DT As DataTable
    End Structure

    Dim DS As DataSet
    Private MSPs As Table
    Private PAYORGs As Table
    Private LISTs As Table
    Private PAYs As Table

    Private Connection As SqlConnection

    Private Sub LoadFromDB(ByVal SQL As String, ByRef DEST As Table)
        With DEST
            .DA = New SqlDataAdapter(SQL, Connection)
            Dim BLD As New SqlCommandBuilder(.DA)
            .DA.DeleteCommand = BLD.GetDeleteCommand
            .DA.InsertCommand = BLD.GetInsertCommand
            .DA.UpdateCommand = BLD.GetUpdateCommand
            .DT = New DataTable
            .DA.FillSchema(.DT, SchemaType.Source)
            .DA.Fill(.DT)
        End With
    End Sub


    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Connection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=E:\Jewel\ReSitex\resitex.mdf;Integrated Security=True;User Instance=True")
        Connection.Open()

        LoadFromDB("SELECT * FROM MSPs", MSPs)
        LoadFromDB("SELECT * FROM PAYORGs", PAYORGs)
        LoadFromDB("SELECT * FROM LISTs", LISTs)
        LoadFromDB("SELECT * FROM PAYs", PAYs)

        DS = New DataSet
        DS.Tables.Add(MSPs.DT)
        DS.Tables.Add(PAYORGs.DT)
        DS.Tables.Add(LISTs.DT)
        DS.Tables.Add(PAYs.DT)

        DS.Relations.Add("LISTs2PAYs", LISTs.DT.Columns("id"), PAYs.DT.Columns("List"))

        dgvLists.DataSource = DS
        dgvLists.DataMember = "LISTs"

        dgvPays.DataSource = DS
        dgvPays.DataMember = "LISTs.LISTs2PAYs"
    End Sub

    Private Sub tsListsCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsListsCreate.Click
        Dim NewRow As DataRow
        NewRow = LISTs.DT.NewRow
        LISTs.DT.Rows.Add(NewRow)
    End Sub

    Private Sub tsPaysSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPaysSave.Click
        If MsgBox("Сохранить изменения?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            dgvPays.EndEdit()
            PAYs.DA.Update(PAYs.DT)
        End If
    End Sub

    Private Sub tsListsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsListsSave.Click
        If MsgBox("Сохранить изменения?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            dgvLists.EndEdit()
            LISTs.DA.Update(LISTs.DT)
        End If
    End Sub

    Private Sub tsPaysOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPaysOpen.Click
        'процедура представляет пользователю возможность выбрать файл CSV (TXT)
        'из этого файла в БД импортируются сведения о получателях и суммах
        With ofdPaysOpen
            .FileName = "E:\Jewel\ReSitex\test.csv"
            .Filter = "ZACH файлы (ZACH*.TXT;*.CSV)|*.TXT;*.CSV|Все файлы (*.*)|*.*"
            .ShowDialog()

            Dim strLine As String
            Dim str() As String
            Dim CurrentFile As New System.IO.StreamReader(.FileName, System.Text.Encoding.GetEncoding(866))
            Do Until CurrentFile.EndOfStream
                strLine = CurrentFile.ReadLine()
                str = Split(strLine, "|")

                Dim NewRow As DataRow
                NewRow = PAYs.DT.NewRow
                NewRow("LIST") = dgvLists.CurrentRow.Cells("id").Value
                NewRow("Счёт") = str(0)
                NewRow("Фамилия") = str(1)
                NewRow("Имя") = str(2)
                NewRow("Отчество") = str(3)
                NewRow("Сумма") = Replace(str(4), ".", ",")
                PAYs.DT.Rows.Add(NewRow)
                MsgBox(strLine)
            Loop
        End With

    End Sub

    Private Sub ofdPaysOpen_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ofdPaysOpen.FileOk

    End Sub
End Class